package com.hcrazy.manchester.app.jdbc;

import java.util.ArrayList;
import java.util.List;

import com.hcrazy.manchester.app.pojo.BookFood;
import com.hcrazy.manchester.app.pojo.Coupon;
import com.hcrazy.manchester.app.pojo.PageModel;

public class CouponDAO extends BaseDAO<Coupon>{

	public long addCoupon(Coupon coupon) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_coupon_coupon(");
		sb.append("name,totalCount,count,type,sellerId,sortId,publishDate,endDate,description_mc,details,thumbnail,pictures,password,sellerName,praiseBaseCount,praiseCount,commentBaseCount,commentCount");
		sb.append(") values (");
		sb.append(":name,:totalCount,:count,:type,:sellerId,:sortId,:publishDate,:endDate,:description_mc,:details,:thumbnail,:pictures,:password,:sellerName,:praiseBaseCount,:praiseCount,:commentBaseCount,:commentCount");
		sb.append(")");
		
		update(sb.toString(), coupon);
		
		return getIncreamentId();
	}
	
	public void updateCoupon(Coupon coupon) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_coupon_coupon set ");
		sb.append(" name=:name,totalCount=:totalCount,count=:count,type=:type,");
		sb.append(" sellerId=:sellerId,sortId=:sortId,publishDate=:publishDate,endDate=:endDate,description_mc=:description_mc,");
		sb.append(" details=:details,thumbnail=:thumbnail,pictures=:pictures,password=:password,sellerName=:sellerName,");
		sb.append(" praiseBaseCount=:praiseBaseCount,praiseCount=:praiseCount,commentBaseCount=:commentBaseCount,commentCount=:commentCount");
		sb.append(" where couponId = :couponId");
		
		update(sb.toString(), coupon);
	}
	
	public List<Coupon> getAllCoupons() throws Exception{
		String sql = "select * from him_coupon_coupon";
		return getList(sql, Coupon.class);
	}
	
	public List<Coupon> getAllCouponsBySortId(int sortId) throws Exception{
		StringBuffer sb = new StringBuffer();
		sb.append("select a.couponId,a.name,a.totalCount,a.count,a.type,a.sellerId,a.sortId,a.publishDate,");
		sb.append("a.endDate,a.description_mc,a.thumbnail,a.pictures,a.password,a.sellerName,");
		sb.append("b.menuName sortName from him_coupon_coupon a,him_book_menu b where a.sortId = b.menuId and a.sortId = ?");
		return getList(sb.toString(), Coupon.class,sortId);
	}
	
	public List<Coupon> getAllCouponsHasDetailBySortId(int sortId) throws Exception{
		String sql = "select a.*,b.menuName sortName from him_coupon_coupon a,him_book_menu b where a.sortId = b.menuId and a.sortId = ?";
		return getList(sql, Coupon.class,sortId);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,int sortId,int sellerId) throws Exception{
		StringBuffer sb = new StringBuffer();
		sb.append("select a.couponId,a.name,a.totalCount,a.count,a.type,a.sellerId,a.sortId,a.publishDate,");
		sb.append("a.endDate,a.description_mc,a.thumbnail,a.pictures,a.password,a.sellerName,");
		sb.append("b.menuName sortName from him_coupon_coupon a,him_book_menu b where a.sortId = b.menuId");
		
		StringBuilder querySQL = new StringBuilder("select * from ("+sb.toString()+") as c");
		StringBuilder countSQL = new StringBuilder("select count(*) from ("+sb.toString()+") as c ");
		StringBuilder whereSQL = new StringBuilder(" where name like ?");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		if(sortId > 0){
			whereSQL.append(" and sortId = ?");
			paramList.add(sortId);
		}
		if(sellerId > 0){
			whereSQL.append(" and sellerId = ?");
			paramList.add(sellerId);
		}
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Coupon.class);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,int sortId,int sellerId,long userId) throws Exception{
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.couponId,a.name,a.totalCount,a.count,a.type,a.sellerId,a.sortId,a.publishDate,c.userId,");
		sb.append(" a.endDate,a.description_mc,a.thumbnail,a.pictures,a.password,a.sellerName,b.menuName sortName,c.`status`");
		sb.append(" from him_coupon_coupon a,him_book_menu b,him_user_coupon c where a.sortId = b.menuId and a.couponId = c.couponId ");
		
		StringBuilder querySQL = new StringBuilder("select * from ("+sb.toString()+") as d");
		StringBuilder countSQL = new StringBuilder("select count(*) from ("+sb.toString()+") as d ");
		StringBuilder whereSQL = new StringBuilder(" where name like ? ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		if(sortId > 0){
			whereSQL.append(" and sortId = ?");
			paramList.add(sortId);
		}
		if(sellerId > 0){
			whereSQL.append(" and sellerId = ?");
			paramList.add(sellerId);
		}
		if(userId > 0){
			whereSQL.append(" and userId = ?");
			paramList.add(userId);
		}
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Coupon.class);
	}
	
	public Coupon getById(int couponId) throws Exception{
		String sql = "select * from him_coupon_coupon where couponId = ?";
		return this.getJavaBean(sql, Coupon.class, couponId);
	}
	
	public void delCoupon(int couponId) throws Exception{
		String sql = "delete from him_coupon_coupon where couponId = ?";
		this.commonUpdate(sql, couponId);
	}
	
	public void addUserCoupon(long userId,int couponId,String receiveTime) throws Exception{
		String sql = "insert into him_user_coupon(userId,couponId,receiveTime) values (?,?,?)";
		commonUpdate(sql, userId,couponId,receiveTime);
	}
	
	public void updateStatus(long userId,int couponId,int status) throws Exception {
		String sql = "update him_user_coupon set status = ? where userId = ? and couponId = ?";
		commonUpdate(sql, status,userId,couponId);
	}
	
	public void deleteUserCoupon(long userId,int couponId) throws Exception {
		String sql = "delete from him_user_coupon where userId = ? and couponId = ?";
		commonUpdate(sql,userId,couponId);
	}
	
	public int getCountByUserIdAndCouponId(long userId,int couponId) throws Exception {
		String sql = "select count(*) from him_user_coupon where userId = ? and couponId = ?";
		return getCount(sql, userId,couponId);
	}
	
	public int getCountByUserId(long userId) throws Exception {
		String sql = "select count(*) from him_user_coupon where userId = ? and status = 0";
		return getCount(sql, userId);
	}
	
	public long getPraiseCount(long couponId) throws Exception{
		String sql = "select praiseCount from him_coupon_coupon where couponId = ?";
		return this.getJdbcTemplate().queryForLong(sql,couponId);
	}
	
	public long getCommentCount(long couponId) throws Exception{
		String sql = "select commentCount from him_coupon_coupon where couponId = ?";
		return this.getJdbcTemplate().queryForLong(sql,couponId);
	}
	
	public void addPraiseCount(long infoId) throws Exception {
		String sql = "update him_coupon_coupon set praiseCount = praiseCount + 1 where couponId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public void addCommentCount(long infoId) throws Exception {
		String sql = "update him_coupon_coupon set commentCount = commentCount + 1 where couponId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	/**
	 * 获取收藏信息
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public PageModel getCollegeInfos(long userId,int moduleType,PageModel model) throws Exception{
		String sql = "select a.type,b.* from him_user_collection a,him_coupon_coupon b where a.infoId = b.couponId";
		StringBuilder querySQL = new StringBuilder("select * from ("+sql+") as m");
		StringBuilder countSQL = new StringBuilder("select count(*) from ("+sql+") as m");
		StringBuilder whereSQL = new StringBuilder(" where userId = ? and type = ?");
		
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(userId);
		paramList.add(moduleType);
		
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, Coupon.class);
	}
	
	public List<Coupon> getAllCoupons(int size) throws Exception{
		String sql = "select * from him_coupon_coupon order by couponId desc limit ?";
		
		return this.getList(sql, Coupon.class,size);
	}
}
